package com.gmrz.uap.model;

import com.gmrz.uap.cfg.SystemConst;
import com.gmrz.uap.common.DBCommon;
import com.gmrz.uap.model.base.BaseDataStatistics;
import com.jfinal.kit.Kv;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * Generated by JFinal.
 */
@SuppressWarnings("serial")
public class DataStatistics extends BaseDataStatistics<DataStatistics> {
    public static final DataStatistics dao = new DataStatistics().dao();


    private static final String NAMESPACE = "dataStatistics";
    private static final String SQL_SELECT_COMM = NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "select_comm";


    /**
     * 根据条件获取统计信息
     *
     * @return 统计对象
     */
    public DataStatistics findOne(DataStatistics dataStatistics) {
        StringBuilder sql = new StringBuilder("select * from t_data_statistics where dstc_id=dstc_id ");
        Iterator<Map.Entry<String, Object>> i = dataStatistics._getAttrsEntrySet().iterator();
        Date time = null;

        //循环遍历传过来的参数，并将参数拼接到SQL
        while (i.hasNext()) {
            Map.Entry<String, Object> obj = i.next();

            if (StrKit.isBlank(obj.getKey()))
                continue;

            //如果数据为空或为NULL的时候，跳过此次循环进入下一次循环
            if (null == obj.getValue() || "" == obj.getValue()) {
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_TENANT_ID, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_STA_TYPE, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_AUTH_TYPE, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_STA_DATE, obj.getKey())) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                try {
                    time = sdf.parse((String) obj.getValue());
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                sql.append(" and " + obj.getKey() + " = ? ");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_AAID, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_TRANS_TYPE, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_OPER_TYPE, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_OS_TYPE, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_OS_VERSION, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_DEVICE_TYPE, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_DEVICE_NAME, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

            if (StringUtils.equalsIgnoreCase(DBCommon.TABLE_DATA_STATISTICS_COLUMN_INSTCODE, obj.getKey())) {
                sql.append(" and " + obj.getKey() + " = '" + obj.getValue() + "'");
                continue;
            }

        }
        if (null != time) {
            return this.findFirst(sql.toString(), new Date(time.getTime()));
        }
        return this.findFirst(sql.toString());
    }

    /**
     * 获取某种统计类型的最后时间
     *
     * @return 统计对象
     */
    public String findLatestDate(DataStatistics dataStatistics) {
        SqlPara sqlPara = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "findLatestDate", dataStatistics);
        return Db.queryStr(sqlPara.getSql());
    }

    /**
     * 获取表中最小的时间
     *
     * @return
     */
    public DateTime getEarliestDate() {
        DateTime dateTime;
        String minDate = Db.queryStr(this.getSql(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getEarliestDate"));

        if (StrKit.isBlank(minDate)) {//如果返回为空代表数据库中还没有数据，则返回当前时间
            dateTime = new DateTime();
        } else {
            dateTime = new DateTime(minDate);
        }
        return dateTime;
    }

    /**
     * 根据统计类型和认证类型获取当前年各个月份的统计数据
     *
     * @param dataStatistics
     * @return 月度统计数据集合
     */
    public List<DataStatistics> getStatisticsCount(DataStatistics dataStatistics) {
        SqlPara sqlPara = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getStatisticsCount", dataStatistics);
        return this.find(sqlPara.getSql());
    }

    /**
     * 获取年度统计数据
     *
     * @param tenantId     渠道名称
     * @param year         年
     * @param authTypeList 认证类型列表
     * @param staType      统计类型
     * @return 年度统计数据集合
     */
    public int getRegCountForYear(String tenantId, Integer year, List<Dict> authTypeList, String staType) {

        Kv cond = Kv.by("tenantId", tenantId).set("year", year).set("authTypeList", authTypeList).set("staType", staType);
        SqlPara sqlPara = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getRegCountForYear", cond);
        Number number = Db.queryNumber(sqlPara.getSql());
        if (number != null) {
            return number.intValue();
        }
        return 0;
    }

    /**
     * 获取手机型号使用排行
     *
     * @param tenantId  渠道名称
     * @param startDate 开始时间
     * @param endDate   结束时间
     * @param page      页数
     * @param rows      行数
     * @return 手机使用排行 Page<Record>
     */
    public Page<Record> getPhoneUse(String tenantId, String startDate, String endDate, int page, int rows, String sort, String order) {
        Kv cond = Kv.by("tenantId", tenantId).set("startDate", startDate).set("endDate", endDate).set("sort", sort).set("order", order);
        SqlPara sqlPara = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getPhoneUse", cond);
        return Db.paginate(page, rows, sqlPara);
    }


    /**
     * 统计注册、认证、注销每日数量
     *
     * @param tenantId  渠道名称
     * @param staType   统计类型
     * @param authType  认证类型
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @param page      页号
     * @param rows      行数
     * @param sort      排序字段
     * @param order     升序/降序
     * @return 统计数据 Page
     */
    public Page<DataStatistics> getStaDateForDay(String tenantId, String staType, String authType, String startDate, String endDate, int page, int rows, String sort, String order) {
        Kv cond = Kv.by("tenantId", tenantId).set("staType", staType).set("authType", authType).set("startDate", startDate).set("endDate", endDate).set("sort", sort).set("order", order);
        SqlPara sqlPara = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getStaDataForDay", cond);
        return this.paginate(page, rows, sqlPara);
    }

    /**
     * 获取年份列表
     *
     * @return
     */
    public List<DataStatistics> getSingleYearList() {
        SqlPara sqlPara = Db.getSqlPara(NAMESPACE + SystemConst._DEFAULT_SEPARATOR_DOT + "getSingleYearList");
        return this.find(sqlPara.getSql());
    }


    public Page<Record> getCertRegister(String tenantId, List<String> aaidList, String deviceTypeAlias, String deviceNameAlias, String startDate, String endDate, int page, int rows, String sort, String order) {
        StringBuffer stringBuffer = new StringBuffer();
        for(int i = 0; i < aaidList.size(); i++){
            stringBuffer.append("'");
            stringBuffer.append(aaidList.get(i));
            stringBuffer.append("'");
            if(i != aaidList.size()-1){
                stringBuffer.append(",");
            }
        }

        String selectsql = " SELECT a.tenant_id,b.device_type_alias,b.device_name_alias , sum(a.sta_count) sta_count ";
        String sql = " from t_data_statistics a left join t_device_alias b on a.device_type = b.device_type and a.device_name = b.device_name\n" +
                "\n" +
                "WHERE a.sta_type = 'certDeviceType' and a.sta_date >= ? and a.sta_date < ? and a.aaid in("+stringBuffer.toString()+")";

        if(null != tenantId && !"".equals(tenantId.trim()) && !"ALL".equals(tenantId.trim())){
            sql = sql + "and a.tenant_id='"+tenantId+"'";
        }
        if(null != deviceTypeAlias && !"".equals(deviceTypeAlias.trim()) && !"ALL".equals(deviceTypeAlias.trim())){
            sql = sql + "and b.device_type_alias='"+deviceTypeAlias+"'";
        }
        if(null != deviceNameAlias && !"".equals(deviceNameAlias.trim()) && !"ALL".equals(deviceNameAlias.trim())){
            sql = sql + "and b.device_name_alias='"+deviceNameAlias+"'";
        }

        String groupBySql = " group BY a.tenant_id,b.device_type_alias,b.device_name_alias order BY a.tenant_id,b.device_type_alias desc ,b.device_name_alias desc ";

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date d1 = null;
        try {
            d1 = simpleDateFormat.parse(startDate);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        Date d2 = null;
        try {
            d2 = simpleDateFormat.parse(endDate);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return Db.paginate(page,rows,selectsql,sql+groupBySql,d1,new Date(d2.getTime()+ (long)86400000));
    }

    public List<Record> getOtherDeviceNameAliasList(String tenantId, List<String> aaidList, String startDate, String endDate) {
        StringBuffer stringBuffer = new StringBuffer();
        for(int i = 0; i < aaidList.size(); i++){
            stringBuffer.append("'");
            stringBuffer.append(aaidList.get(i));
            stringBuffer.append("'");
            if(i != aaidList.size()-1){
                stringBuffer.append(",");
            }
        }

        String selectsql = " SELECT a.tenant_id,a.device_type,a.device_name , sum(a.sta_count) sta_count ";
        String sql = " from t_data_statistics a left join t_device_alias b on a.device_type = b.device_type and a.device_name = b.device_name\n" +
                "\n" +
                "WHERE a.sta_type = 'certDeviceType' and a.sta_date >= ? and a.sta_date < ? and a.aaid in("+stringBuffer.toString()+")";

        if(null != tenantId && !"".equals(tenantId.trim()) && !"ALL".equals(tenantId.trim())){
            sql = sql + "and a.tenant_id='"+tenantId+"'";
        }

        sql = sql + "and b.device_type_alias is null";

        String groupBySql = " group BY a.tenant_id,a.device_type,a.device_name order BY a.tenant_id,a.device_type desc ,a.device_name desc ";

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date d1 = null;
        try {
            d1 = simpleDateFormat.parse(startDate);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        Date d2 = null;
        try {
            d2 = simpleDateFormat.parse(endDate);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return Db.find(selectsql+sql+groupBySql,d1,new Date(d2.getTime()+ (long)86400000));
    }



    public Page<Record> getCertUseCase(String instCodes, boolean permissions, int page, int rows) {
        String selectsql = "select a.inst_code, a.aaid , a.use_count, d.dict_text  ";
        String sql = null;
        if (permissions){
            sql = " from (select inst_code, aaid , SUM(sta_count) use_count \n" +
                    "from t_data_statistics where sta_type='certUseCase'  \n" +
                    "group by inst_code, aaid order by inst_code desc) a \n" +
                    "left join (select dict_name, dict_text from t_dict where dt_id = '00dcb889f5364e5f850b79617cae168d') d \n" +
                    "on a.aaid = d.dict_name ";
        }else {
            StringBuffer stringBuffer = new StringBuffer();
            if (instCodes != null && instCodes.contains(",")){
                String[] codes = instCodes.split(",");
                for(int i = 0; i < codes.length; i++){
                    stringBuffer.append("'");
                    stringBuffer.append(codes[i]);
                    stringBuffer.append("'");
                    if(i != codes.length-1){
                        stringBuffer.append(",");
                    }
                }
            }else {
                stringBuffer.append("'" + instCodes + "'");
            }
            sql = " from (select inst_code, aaid , SUM(sta_count) use_count \n" +
                    "from t_data_statistics where sta_type='certUseCase' and inst_code in ( "+stringBuffer.toString()+" ) \n" +
                    "group by inst_code, aaid order by inst_code desc) a \n" +
                    "left join (select dict_name, dict_text from t_dict where dt_id = '00dcb889f5364e5f850b79617cae168d') d \n" +
                    "on a.aaid = d.dict_name ";
        }

        return Db.paginate(page, rows, selectsql, sql);
    }

}
